package com.dao.front.Impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.dao.BaseDao;
import com.dao.front.ISingerDaoFront;
import com.dao.front.ISpecialDaoFront;
import com.entity.Data.PagerData;
@Repository()
public class SpecialDaoFrontImpl extends BaseDao implements ISpecialDaoFront {

	@Override
	public List<Map<String, Object>> findAll() {
		String sql="select s.specialId,s.specialName,s.specialType,s.language,s.issueDate,s.recordCompany,s.specialPhoto,s.specialIntro,"
                +"g.singerId,g.singerName,g.seName,g.state,g.address,g.work,g.birthday,g.singerPhoto,"
                +"m.musicId,m.musicName,m.timeLength,m.musicMp3 "
				+ " from special as s "
				+ " left join music as m  "
				+ " on m.specialId = s.specialId"
				+ " left join singer as g   "
				+ " on m.singerId=g.singerId";
		// TODO Auto-generated method stub
		return executeQuery(sql, null);
	}

	@Override
	public PagerData findByPage(int curPage, int pageSize) {
		// TODO Auto-generated method stub
		//总共有几条数据
				String sqlTotal = "select  "
						+ "		count(*) "
						+ " from special as s "
						+ " left join music as m  "
						+ " on m.specialId = s.specialId"
						+ " left join singer as g   "
						+ " on m.singerId=g.singerId";
				
				
				int totalCount = jdbcTemplate.queryForInt(sqlTotal);
				int totalPage = totalCount%pageSize==0?
					    totalCount/pageSize   :
					    totalCount/pageSize+1 ;
				String sql="select s.specialId,s.specialName,s.specialType,s.language,s.issueDate,s.recordCompany,s.specialPhoto,s.specialIntro,"
		                +" g.singerId,g.singerName,g.seName,g.state,g.address,g.work,g.birthday,g.singerPhoto,"
		                +" m.musicId,m.musicName,m.timeLength,m.musicMp3 "
						+ " from special as s "
						+ " left join music as m  "
						+ " on m.specialId = s.specialId"
						+ " left join singer as g   "
						+ " on m.singerId=g.singerId"
						+ " group by s.specialName"
						+ " order by s.specialId "
						+ " limit ?,? ";
				int start = (curPage-1)*pageSize;
				
				List<Object> params = new ArrayList<Object>();
				params.add(start);
				params.add(pageSize);
				
				//查询当前页的数据记录
				List<Map<String,Object>> dataList = executeQuery(sql,params.toArray());
				
				//将数据封装到分页数据对象中
				PagerData pd = new PagerData();
				pd.setCurPage(curPage); //当前第几页
				pd.setPageSize(pageSize); //每页几条
				pd.setTotalCount(totalCount);//共几条数据
				pd.setTotalPage(totalPage); //共几页
				pd.setDataList(dataList); //当前页的数据
				
				return pd;
				
		
	}

	@Override
	public Map<String, Object> find(int musicId) {
		String sql="select s.specialId,s.specialName,s.specialType,s.language,s.issueDate,s.recordCompany,s.specialPhoto,s.specialIntro,"
                +"g.singerId,g.singerName,g.seName,g.state,g.address,g.work,g.birthday,g.singerPhoto,"
                +"m.musicId,m.musicName,m.timeLength,m.musicMp3 "
				+ " from special as s "
				+ " left join music as m  "
				+ " on m.specialId = s.specialId"
				+ " left join singer as g   "
				+ " on m.singerId=g.singerId"
				+ " where m.musicId=? ";
		Object[] params={musicId};
		return executeQueryMap(sql, params);
	}

	@Override
	public List<Map<String, Object>> findSpe(int specialId) {
		// TODO Auto-generated method stub
		String sql="select s.specialId,s.specialName,s.specialType,s.language,s.issueDate,s.recordCompany,s.specialPhoto,s.specialIntro,"
                +"g.singerId,g.singerName,g.seName,g.state,g.address,g.work,g.birthday,g.singerPhoto,"
                +"m.musicId,m.musicName,m.timeLength,m.musicMp3 "
				+ " from special as s "
				+ " left join music as m  "
				+ " on m.specialId = s.specialId"
				+ " left join singer as g   "
				+ " on m.singerId=g.singerId "
				+ " where s.specialId=?";
		Object[] params={specialId};
		return executeQuery(sql, params);
	}

	

}
